# -*- coding: utf-8 -*-
"""
Created on Fri May 17 12:57:38 2019

http://pandas.pydata.org/pandas-docs/stable/getting_started/10min.html

@author: yaoyu
"""

import numpy as np
import pandas as pd

# 对象创建
s = pd.Series([1,3,5,np.nan,6,8])
print(s)

dates = pd.date_range('20190101', periods=6)
print(dates)

df = pd.DataFrame(np.random.randn(6,4), index=dates, columns=list('ABCD'))
print(df)

df2 = pd.DataFrame({'A': 1.,
                    'B': pd.Timestamp('20130102'),
                    'C': pd.Series(1, index=list(range(4)), dtype='float32'),
                    'D': np.array([3] * 4, dtype='int32'),
                    'E': pd.Categorical(["test", "train", "test", "train"]),
                    'F': 'foo'})
print(df2)
print(df2.dtypes)

# 数据显示
print(df.head())
print(df.tail(3))
print(df.index)
print(df.columns)

print(df.to_numpy())
print(df.describe())
# 转置矩阵
print(df.T)

# 排序
# sort_index (作用：默认根据行标签对所有行排序，或根据列标签对所有列排序，或根据指定某列或某几列对行排序。)
print(df.sort_index(axis=1, ascending=False))

# sort_values 按列值排序
print(df.sort_values(by='B'))

# 选择
## 选择列
print(df['A'] == df.A)

## 选择行
print(df[0:3])
print(df['20190102':'20190104'])

## 通过标签选择
print(df.loc[dates[0]])

## 多轴
print(df.loc[:,['A', 'B']])
print(df.loc['20190102':'20190104', ['A', 'B']])
print(df.loc['20190102', ['A', 'B']])

## 得到标量值
print(df.loc[dates[0], 'A'])
## 快速得到标量值
print(df.at[dates[0], 'A'])

# 按位置选择
print(df.iloc[3])
print(df.iloc[3:5, 0:2])
print(df.iloc[[1, 2, 4], [0, 2]])
print(df.iloc[1:3, :])
print(df.iloc[:, 1:3])
print(df.iloc[1, 1])
print(df.iat[1, 1])

# 按布尔值索引
print(df[df.A > 0])
print(df[df>0])

df2 = df.copy()
df2['E'] = ['one', 'one', 'two', 'three', 'four', 'three']
print(df2)
print(df2[df2['E'].isin(['two', 'four'])])

# 值设置(需要对应)
s1 = pd.Series([1, 2, 3, 4, 5, 6], index=pd.date_range('20190102', periods=6))
df['F'] = s1
print(df2)

df2.at[dates[0], 'A'] = 0
print(df2)
df.iat[0, 1] = 0
df.loc[:, 'D'] = np.array([5] * len(df))
print(df)

df2 = df.copy()
df2[df2 > 0] = -df2
print(df2)

# 缺失值
df1 = df.reindex(index=dates[0:4], columns=list(df.columns) + ['E'])
df1.loc[dates[0]:dates[1], 'E'] = 1
print(df1)
print(df1.dropna(how='any'))
print(df1.fillna(value=5))
print(pd.isna(df1))

# 操作

## 统计操作(会排除缺失值)
print(df.mean())    #列平均
print(df.mean(1))   #行平均

# 数据对齐
s = pd.Series([1, 3, 5, np.nan, 6, 8], index=dates).shift(2)
print(s)
print(df)
print(df.sub(s, axis='index'))

'''
In [65]: df.sub(s, axis='index')
Out[65]: 
                   A         B         C    D    F
2013-01-01       NaN       NaN       NaN  NaN  NaN
2013-01-02       NaN       NaN       NaN  NaN  NaN
2013-01-03 -1.861849 -3.104569 -1.494929  4.0  1.0
2013-01-04 -2.278445 -3.706771 -4.039575  2.0  0.0
2013-01-05 -5.424972 -4.432980 -4.723768  0.0 -1.0
2013-01-06       NaN       NaN       NaN  NaN  NaN
'''

## Apply(将函数应用到数据上)
print(df.apply(np.cumsum))
print(df.apply(lambda x: x.max() - x.min()))

## Histogramming
s = pd.Series(np.random.randint(0, 7, size=10))
print(s)
print(s.value_counts())

## 字符串方法
s = pd.Series(['A', 'B', 'C', 'Aaba', 'Baca', np.nan, 'CABA', 'dog', 'cat'])
print(s.str.lower())

# 数据合并

## concat
df = pd.DataFrame(np.random.randn(10, 4))
print(df)

# break it into pieces
pieces = [df[:3], df[3:7], df[7:]]
print(pieces)
print(pd.concat(pieces))

## join(SQL概念)
left = pd.DataFrame({'key': ['foo', 'foo'], 'lval': [1, 2]})
right = pd.DataFrame({'key': ['foo', 'foo'], 'rval': [4, 5]})
print(left)
print(right)
print(pd.merge(left, right))

left = pd.DataFrame({'key': ['foo', 'bar'], 'lval': [1, 2]})
right = pd.DataFrame({'key': ['foo', 'bar'], 'rval': [4, 5]})
print(pd.merge(left, right, on='key'))

## Append
df = pd.DataFrame(np.random.randn(8, 4), columns=['A', 'B', 'C', 'D'])
print(df)
s = df.iloc[3]
print(s)
print(df.append(s, ignore_index=True))
print(df)

# Grouping
df = pd.DataFrame({'A': ['foo', 'bar', 'foo', 'bar', 'foo', 'bar', 'foo', 'foo'],
                    'B': ['one', 'one', 'two', 'three', 'two', 'two', 'one', 'three'],
                    'C': np.random.randn(8),
                    'D': np.random.randn(8)})
print(df)
print(df.groupby('A').sum())
print(df.groupby(['A','B']).sum())

## Reshaping
tuples = list(zip(*[['bar', 'bar', 'baz', 'baz',
                      'foo', 'foo', 'qux', 'qux'],
                     ['one', 'two', 'one', 'two',
                      'one', 'two', 'one', 'two']]))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
df = pd.DataFrame(np.random.randn(8, 2), index=index, columns=['A', 'B'])
print(df)
df2=df[:4]
print(df2)
stacked = df2.stack()
print(stacked)
print(stacked.unstack())
print(stacked.unstack(1))

## Pivot Tables
df = pd.DataFrame({'A': ['one', 'one', 'two', 'three'] * 3,
                    'B': ['A', 'B', 'C'] * 4,
                    'C': ['foo', 'foo', 'foo', 'bar', 'bar', 'bar'] * 2,
                    'D': np.random.randn(12),
                    'E': np.random.randn(12)})
print(df)
print(pd.pivot_table(df, values='D', index=['A', 'B'], columns=['C']))

## 时间序列
rng = pd.date_range('1/1/2012', periods=100, freq='D')
ts = pd.Series(np.random.randint(0, 500, len(rng)), index=rng)
print(ts)
